import scrapy, datetime, re
from scrapy.loader import ItemLoader
from scrapy.loader.processors import MapCompose, TakeFirst, Join
from ArticleSpider.utils.common import extract_num
from ArticleSpider.settings import SQL_DATETIME_FORMAT,SQL_DATE_FORMAT
from w3lib.html import remove_tags
class ArticlespiderItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    pass


# 由于使用了ItemLoader类，所以item的每一个值会被以列表的方式返回，但是多数情况我们只需要第一个值
# 重构ItemLoader类，默认返回第一个值
class ArticleItemLoader(ItemLoader):
    # 通过自定义itemloader要求都取列表中第一个值
    default_output_processor = TakeFirst()


# 处理时间格式
def date_convert(value):
    try:
        create_date = datetime.datetime.strptime(value, "%Y/%m/%d").date()  # 万能的日期格式转换
    except Exception as e:
        create_date = datetime.datetime.now()
    return create_date


# 获取点赞评论数量
def get_nums(value):
    match_re = re.match(".*?(\d+).*", value)
    if match_re:
        nums = match_re.group(1)
    else:
        nums = 0
    return nums


# 去掉tags中的评论字段
def remove_comment_tags(value):
    if "评论" in value:
        return ""
    else:
        return value


def return_value(value):
    # 覆盖掉default_output_processor = TakeFirst()操作
    return value


# 伯乐在线数据处理
class JobBoleArticleItem(scrapy.Item):
    title = scrapy.Field()  # item中只有一种数据类型，部分str、int...
    create_date = scrapy.Field(
        input_processor=MapCompose(date_convert)
    )
    front_image_url = scrapy.Field(
        output_processor=MapCompose(return_value)
    )
    front_image_path = scrapy.Field()
    url = scrapy.Field()
    url_object_id = scrapy.Field()
    praise_nums = scrapy.Field()
    fav_nums = scrapy.Field(
        input_processor=MapCompose(get_nums)
    )
    comment_nums = scrapy.Field(
        input_processor=MapCompose(get_nums)
    )
    tags = scrapy.Field(
        input_processor=MapCompose(remove_comment_tags),
        output_processor=Join(',')
    )
    content = scrapy.Field()
    def get_insert_sql(self):
        insert_sql = """
                    insert into jobbole_article(title,create_date,url,url_object_id,front_image_url,front_image_path,comment_nums,fav_nums,praise_nums,tags,content)
                    values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    """
        params=(self['title'], self['create_date'], self['url'], self['url_object_id'],self['front_image_url'],self['front_image_path'],self['comment_nums'],self['fav_nums'],self['praise_nums'],self['tags'],self['content'])
        return insert_sql,params

# 知乎数据处理-文章详情
class ZhihuQuestionItem(scrapy.Item):
    zhihu_id = scrapy.Field()
    topics= scrapy.Field()
    url= scrapy.Field()
    title= scrapy.Field()
    content= scrapy.Field()
    answer_num= scrapy.Field()
    comments_num= scrapy.Field()
    watch_user_num= scrapy.Field()
    click_num= scrapy.Field()
    crawl_time= scrapy.Field()
    def get_insert_sql(self):
        insert_sql = """
                    insert into zhihu_question(zhihu_id, topics, url, title, content, answer_num, comments_num,
                      watch_user_num, click_num, crawl_time
                      )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE content=VALUES(content), answer_num=VALUES(answer_num), comments_num=VALUES(comments_num),
                      watch_user_num=VALUES(watch_user_num), click_num=VALUES(click_num)
                """
        zhihu_id = self["zhihu_id"][0]
        topics = ",".join(self["topics"])
        url = self["url"][0]
        title = "".join(self["title"])
        content = "".join(self["content"])
        answer_num = extract_num("".join(self["answer_num"]))
        comments_num = extract_num("".join(self["comments_num"]))

        if len(self["watch_user_num"]) == 2:
            watch_user_num = int(self["watch_user_num"][0].replace(',',''))
            click_num = int(self["watch_user_num"][1].replace(',',''))
        else:
            watch_user_num = int(self["watch_user_num"][0].replace(',',''))
            click_num = 0

        crawl_time = datetime.datetime.now().strftime(SQL_DATETIME_FORMAT)

        params = (zhihu_id, topics, url, title, content, answer_num, comments_num,
                  watch_user_num, click_num, crawl_time)

        return insert_sql,params

# 知乎数据处理-回答详情
class ZhihuAnswerItem(scrapy.Item):
    zhihu_id = scrapy.Field()
    url = scrapy.Field()
    question_id= scrapy.Field()
    author_id= scrapy.Field()
    content= scrapy.Field()
    praise_num= scrapy.Field()
    comments_num= scrapy.Field()
    create_time= scrapy.Field()
    update_time= scrapy.Field()
    crawl_time= scrapy.Field()
    def get_insert_sql(self):
        #插入知乎question表的sql语句
        insert_sql = """
            insert into zhihu_answer(zhihu_id, url, question_id, author_id, content, praise_num, comments_num,
              create_time, update_time, crawl_time
              ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
              ON DUPLICATE KEY UPDATE content=VALUES(content), comments_num=VALUES(comments_num), praise_num=VALUES(praise_num),
              update_time=VALUES(update_time)
        """

        create_time = datetime.datetime.fromtimestamp(self["create_time"]).strftime(SQL_DATETIME_FORMAT)
        update_time = datetime.datetime.fromtimestamp(self["update_time"]).strftime(SQL_DATETIME_FORMAT)
        params = (
            self["zhihu_id"], self["url"], self["question_id"],
            self["author_id"], self["content"], self["praise_num"],
            self["comments_num"], create_time, update_time,
            self["crawl_time"].strftime(SQL_DATETIME_FORMAT),
        )

        return insert_sql, params


def replace_splash(value):
    return value.replace("/", "")
def handle_strip(value):
    return value.strip()
def handle_jobaddr(value):
    addr_list = value.split("\n")
    addr_list = [item.strip() for item in addr_list if item.strip() != "查看地图"]
    return "".join(addr_list)

class LagouJobItemLoader(ItemLoader):
    # 通过自定义itemloader要求都取列表中第一个值
    default_output_processor = TakeFirst()
#拉钩网
class LagouJobItem(scrapy.Item):
    #拉勾网职位
    title = scrapy.Field()
    url = scrapy.Field()
    url_object_id = scrapy.Field()
    job_city = scrapy.Field(
        input_processor=MapCompose(replace_splash),
    )
    work_years = scrapy.Field(
        input_processor=MapCompose(replace_splash),
    )
    degree_need = scrapy.Field(
        input_processor=MapCompose(replace_splash),
    )
    job_type = scrapy.Field()
    max_salary = scrapy.Field()
    min_salary = scrapy.Field()
    publish_time = scrapy.Field()
    job_advantage = scrapy.Field()
    job_desc = scrapy.Field(
        input_processor=MapCompose(handle_strip),
    )
    job_addr = scrapy.Field(
        input_processor=MapCompose(remove_tags, handle_jobaddr),
    )
    company_name = scrapy.Field(
        input_processor=MapCompose(handle_strip),
    )
    company_url = scrapy.Field()
    crawl_time = scrapy.Field()
    crawl_update_time = scrapy.Field()
    tags = scrapy.Field()
    def get_insert_sql(self):
        insert_sql = """
            insert into lagou_job(tags,crawl_time,crawl_update_time,min_salary,max_salary,title, url,url_object_id,job_city, work_years, degree_need,
            job_type, publish_time, job_advantage, job_desc, job_addr, company_url, company_name, job_id)
            VALUES (%s,%s,%s,%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE job_desc=VALUES(job_desc),crawl_update_time=VALUES(crawl_update_time)
        """

        job_id = extract_num(self["url"])
        params = (self["tags"],self["crawl_time"],self["crawl_update_time"],self["min_salary"],self["max_salary"],self["title"], self["url"], self["url_object_id"], self["job_city"], self["work_years"], self["degree_need"],
                  self["job_type"], self["publish_time"], self["job_advantage"], self["job_desc"], self["job_addr"], self["company_url"],
                  self["company_name"], job_id)

        return insert_sql, params